﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;

namespace WindowsFormsApplication6
{
    public class Provider
    {
        public List<string> Tables;

        public Provider()
        {
            Tables = new List<string>();
        }

        //public void ConfigureScope(string hostName,string dbName, string scopeName)
        public void ConfigureScope(string connString, string scopeName)
        {   
            //System.Data.SqlClient.SqlConnection conn = new SqlConnection(GetConnectionString(hostName,dbName));
            System.Data.SqlClient.SqlConnection conn = new SqlConnection(connString);

            conn.Open();
            RemoveScope(conn, scopeName, "");
            conn.Close();

            SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning();
            //serverConfig.ObjectPrefix = "sync";

            //determine if this scope already exists on the server and if not go ahead and provision

            if (!serverConfig.ScopeExists(scopeName, conn))
            {

                //add the approrpiate tables to this scope

                //scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("trans_hed", (System.Data.SqlClient.SqlConnection)provider.Connection));

               /* DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scopeName);

                foreach (Sync.Library.Table table in TablesToSync)
                {
                    System.Collections.ObjectModel.Collection<string> columns = new System.Collections.ObjectModel.Collection<string>();
                    columns.Add("area_id");
                    columns.Add("area_cod");
                    columns.Add("area_desc");


                    DbSyncTableDescription tableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("areas", columns, conn);

                    scopeDesc.Tables.Add(tableDescription);
                }
                */
                DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scopeName);

                foreach (Sync.Library.Table table in TablesToSync)
                {
                    var columns = table.Columns.Select<Sync.Library.Column, string>(i => i.Name).ToList();

                    DbSyncTableDescription tableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable(table.Name, new System.Collections.ObjectModel.Collection<string>(columns), conn);

                    scopeDesc.Tables.Add(tableDescription);
                }

                //scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("order_details", (System.Data.SqlClient.SqlConnection)provider.Connection));



                //note that it is important to call this after the tables have been added to the scope

                serverConfig.PopulateFromScopeDescription(scopeDesc);



                //indicate that the base table already exists and does not need to be created

                serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
                serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Create);
                serverConfig.SetCreateTriggersDefault(DbSyncCreationOption.Create);
                serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.Create);
                serverConfig.SetPopulateTrackingTableDefault(DbSyncCreationOption.Create);
                serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);

//                string scr = serverConfig.Script("RetailMain");

                /*serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
                serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Create);
                serverConfig.SetCreateTriggersDefault(DbSyncCreationOption.Create);
                serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.Create);*/


                //provision the server

                serverConfig.Apply(conn);

            }
        }


        public IEnumerable<Sync.Library.Table> TablesToSync;
        
        //public SqlSyncProvider ConfigureSqlSyncProvider(string hostName,string dbName,string scopeName)
        public SqlSyncProvider ConfigureSqlSyncProvider(string connString, string scopeName)
        {
            SqlSyncProvider provider = new SqlSyncProvider();

            provider.Connection = new SqlConnection(connString);

            provider.ScopeName = scopeName;

            /*Microsoft.Synchronization.Data.DbSyncColumnMapping map = new DbSyncColumnMapping("area_desc","area_desc");
            Microsoft.Synchronization.Data.DbSyncColumnMappingCollection mappingColumns = new DbSyncColumnMappingCollection();
            mappingColumns.Add(map);*/


            return provider;

        }

        private string GetConnectionString(string hostName, string dbName)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = hostName;
            builder.InitialCatalog = dbName;
            builder.UserID = "sa";
            builder.Password = "sa22";
            builder.ConnectTimeout = 1;
            return builder.ToString();
        }

        private void RemoveScope(SqlConnection Conn, string scopeName,string prefix)
        {
            foreach (var table in TablesToSync)
            {
                SqlCommand dropTracking = new SqlCommand(@"
      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"_tracking]') AND type in (N'U'))
      DROP TABLE [dbo].[" + table.Name + "_tracking]", Conn);
                dropTracking.ExecuteNonQuery();

                SqlCommand dropTriggers = new SqlCommand(@"
      IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"_delete_trigger]'))
      DROP TRIGGER [dbo].[" + table.Name + @"_delete_trigger];

      IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"_insert_trigger]'))
      DROP TRIGGER [dbo].[" + table.Name + @"_insert_trigger];

      IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"_update_trigger]'))
      DROP TRIGGER [dbo].[" + table.Name + @"_update_trigger];
    ", Conn);
                dropTriggers.ExecuteNonQuery();

                SqlCommand dropStoredProc = new SqlCommand(@"
      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"_delete]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [dbo].[" + table.Name + @"_delete];

      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"_deletemetadata]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [dbo].[" + table.Name + @"_deletemetadata];

      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"_insert]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [dbo].[" + table.Name + @"_insert];

      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"_insertmetadata]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [dbo].[" + table.Name + @"_insertmetadata];

      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"_selectchanges]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [dbo].[" + table.Name + @"_selectchanges];

      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"_selectrow]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [dbo].[" + table.Name + @"_selectrow];

      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"_update]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [dbo].[" + table.Name + @"_update];

      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"_updatemetadata]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [dbo].[" + table.Name + @"_updatemetadata];
    ", Conn);
                dropStoredProc.ExecuteNonQuery();
            }

            SqlCommand getScopeGuid = new SqlCommand(@"
      USE [" + Conn.Database + @"]
      SELECT scope_config_id FROM scope_info WHERE scope_name = '" + scopeName + "'", Conn);

            var reader = getScopeGuid.ExecuteReader();

            if (reader.HasRows)
            {
                reader.Read();

                var id = reader.GetGuid(0);

                reader.Close();

                SqlCommand deleteScope = new SqlCommand(@"
        DELETE FROM scope_info WHERE scope_config_id = '" + id + @"';
        DELETE FROM scope_config WHERE config_id = '" + id + @"';
      ", Conn);
                deleteScope.ExecuteNonQuery();
            }

//            if (ShowAlert)
//                MessageBox.Show("Scope has been removed");
        }



    }
}
